package com.ruoyi.dataserver.service.impl;

import com.ruoyi.dataserver.service.IDataManagerService;
import com.ruoyi.dataserver.model.DATAMANAGER;
import com.ruoyi.dataserver.model.MAPTABLE;
import com.ruoyi.dataserver.service.ISearchTableService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import java.util.List;


/**
 * @ProjectName
 * @Package com.lab.dataserver.service.impl
 * @ClassName DataManagerServceImpl
 * @Description 数据管理逻辑层
 * @Company lab
 * @Author wangzhx
 * @Date 2022/3/3  11:55
 * @UpdateUser
 * @UpdateDate
 * @UpdateRemark
 * @Version 1.0.0
 */
@Slf4j
@Component
public class DataManagerServceImpl implements IDataManagerService {

    @Autowired
    public JdbcTemplate jdbcTemplate;

    @Autowired
    public ISearchTableService tableService;


    @Override
    public String deal() {
        try{
            String sql = "select SMTABLENAME,SMDATASETNAME from smregister where 1 = 1";
            //String sql = "select SMTABLENAME,SMDATASETNAME from smregister where smdatasetid > 15";
            RowMapper<MAPTABLE> tableRowMapper  = new BeanPropertyRowMapper<MAPTABLE>(MAPTABLE.class);
            RowMapper<DATAMANAGER> rowMapper  = new BeanPropertyRowMapper<DATAMANAGER>(DATAMANAGER.class);
            List<MAPTABLE> tableList = jdbcTemplate.query(sql,tableRowMapper);
            tableList.forEach(table -> {
                String dataName = table.getSMDATASETNAME();
                String tableName = table.getSMTABLENAME();
                if(dataName.contains("_LINE")){  //线表
                    //String sqlUpdate = "ALTER TABLE "+tableName+" ADD INDEX "+tableName+"_qddh (QDDH)";
                    String sqlUpdate = "CREATE INDEX "+tableName+"_qddh  on \""+tableName+"\" (QDDH)";
                    jdbcTemplate.execute(sqlUpdate);
                    //String sqlUpdate1 = "ALTER TABLE "+tableName+" ADD INDEX "+tableName+"_zddh (ZDDH)";
                    String sqlUpdate1 = "CREATE INDEX "+tableName+"_zddh  on \""+tableName+"\" (ZDDH)";
                    jdbcTemplate.execute(sqlUpdate1);
                    //String sqlUpdate2 = "ALTER TABLE "+tableName+" ADD INDEX "+tableName+"_lx (LX)";
                    String sqlUpdate2 = "CREATE INDEX "+tableName+"_lx  on \""+tableName+"\" (LX)";
                    jdbcTemplate.execute(sqlUpdate2);
                    //String sqlUpdate3 = "ALTER TABLE "+tableName+" ADD INDEX "+tableName+"_dm (DM)";
                    String sqlUpdate3 = "CREATE INDEX "+tableName+"dm  on \""+tableName+"\" (DM)";
                    jdbcTemplate.execute(sqlUpdate3);
                    //String sqlUpdate4 = "ALTER TABLE "+tableName+" ADD INDEX "+tableName+"_snodid (SNODID)";
                    String sqlUpdate4 = "CREATE INDEX "+tableName+"_snodid  on \""+tableName+"\" (SNODID)";
                    jdbcTemplate.execute(sqlUpdate4);
                    //String sqlUpdate5 = "ALTER TABLE "+tableName+" ADD INDEX "+tableName+"_enodid (ENODID)";
                    String sqlUpdate5 = "CREATE INDEX "+tableName+"_enodid  on \""+tableName+"\" (ENODID)";
                    jdbcTemplate.execute(sqlUpdate5);
                    //String sqlUpdate6 = "ALTER TABLE "+tableName+" ADD INDEX "+tableName+"_usid (US_ID)";
                    String sqlUpdate6 = "CREATE INDEX "+tableName+"_usid  on \""+tableName+"\" (US_ID)";
                    jdbcTemplate.execute(sqlUpdate6);

                    System.out.println(dataName + "完成");
                }else if(dataName.contains("_POINT")){   //点表
                    String sqlUpdate = "CREATE INDEX "+tableName+"_index  on \""+tableName+"\" (WTDH)";
                    jdbcTemplate.execute(sqlUpdate);



                    System.out.println(dataName + "完成");
                }
            });
            return "success";
        } catch (Exception e) {
            return "fail";
        }


    }

    @Override
    public String node() {
        try {
            String sql = "select SMTABLENAME,SMDATASETNAME from smregister where 1 = 1";
            //String sql = "select SMTABLENAME,SMDATASETNAME from smregister where smdatasetid > 40";
            RowMapper<MAPTABLE> tableRowMapper = new BeanPropertyRowMapper<MAPTABLE>(MAPTABLE.class);
            List<MAPTABLE> tableList = jdbcTemplate.query(sql, tableRowMapper);
            tableList.forEach(table -> {
                String dataName = table.getSMDATASETNAME();
                String tableName = table.getSMTABLENAME();

                if (dataName.contains("LINE")) {  //线表
                    /*String sqlUpdate = "update " + tableName + " set snodid = concat(SmSdriN , '-' ,SmSdriE) , enodid = concat(SmSdriS , '-' ,SmSdriW) where 1 = 1";
                    jdbcTemplate.execute(sqlUpdate);*/
                } else if (dataName.contains("_POINT")) {   //点表
                    //System.out.println(dataName + "开始");
                    //if(dataName.contains("GDX_POINT")) {
                    String sqlUpdate = "update  \"" + tableName + "\"    set  nodeid= concat( substr(n_y, 0, INSTR(n_y,'.')+6) ,'-',  substr(n_x, 0, INSTR(n_x,'.')+6) )";
                    jdbcTemplate.execute(sqlUpdate);

                    /*String sqlDelete = "DELETE FROM " + tableName + " AS ta WHERE ta.BSM <> (SELECT t.maxid FROM ( SELECT max( tb.BSM ) AS maxid FROM " + tableName + " AS tb WHERE ta.WTDH = tb.WTDH ) t " +
                            " );";
                    jdbcTemplate.execute(sqlDelete);*/

                        System.out.println(dataName + "完成");
                   // }
                }

            });
            return "success";
        } catch (Exception e) {
            return "fail";
        }
    }
    @Override
    public String line() {
        try {
            String sql = "select SMTABLENAME,SMDATASETNAME from smregister where 1 = 1";
            RowMapper<MAPTABLE> tableRowMapper = new BeanPropertyRowMapper<MAPTABLE>(MAPTABLE.class);
            List<MAPTABLE> tableList = jdbcTemplate.query(sql, tableRowMapper);
            tableList.forEach(table -> {
                String dataName = table.getSMDATASETNAME();
                String tableName = table.getSMTABLENAME();
                if (dataName.contains("_LINE")) {  //线表
                    String pointName = dataName.split("_")[0]+"_POINT";
                    String sqlp = "select SMTABLENAME,SMDATASETNAME from smregister where SMDATASETNAME = '"+pointName+"'";
                    List<MAPTABLE> pointtableList = jdbcTemplate.query(sqlp, tableRowMapper);
                    String pointTableName = pointtableList.get(0).getSMTABLENAME();

                    String sqlUpdate = "update \"" + tableName + "\" t set t.snodid = (SELECT d.NODEID from \""+pointTableName+"\" d where d.WTDH = t.QDDH) , t.enodid = (SELECT d.nodeid from \""+pointTableName+"\" d where d.wtdh = t.ZDDH)";
                    jdbcTemplate.execute(sqlUpdate);
                    System.out.println(sqlUpdate );
                    System.out.println(dataName + "完成");
                }

            });
            return "success";
        } catch (Exception e) {
            return "fail";
        }
    }
}
